Q2沉默会员
/**建表*/
drop table if exists dws.dws_member_silent_day;
create table dws.dws_member_silent_day(
`device_id` string,
`uid` string,
`app_v` string,
`os_type` string,
`language` string,
`channel` string,
`area` string,
`brand` string,
`cnt` int,
`first_dt` string
 ) comment '沉默会员明细'
partitioned by(dt string)
stored as parquet;

drop table if exists ads.ads_member_silent_cnt;
create table ads.ads_member_silent_cnt(
    dt string,
    silent_cnt int
)

--沉默会员：只在安装当天启动过App，而且安装时间是在7天前
--dws
insert overwrite table dws.dws_member_silent_day
partition(dt='2020-07-28')
select device_id,
    concat_ws('|', collect_set(uid)) uid,
    concat_ws('|', collect_set(app_v)) app_v,
    concat_ws('|', collect_set(os_type)) os_type,
    concat_ws('|', collect_set(language)) language,
    concat_ws('|', collect_set(channel)) channel,
    concat_ws('|', collect_set(area)) area,
    concat_ws('|', collect_set(brand)) brand, 
    count(*) cnt, min(dt) first_dt from dws_member_start_day group by device_id
having cnt=1 and datediff('2020-07-28', first_dt)>7;
--ads
insert overwrite table ads.ads_member_silent_cnt
select dt, count(*) silent_cnt from dws.dws_member_silent_day group by dt order by dt;
